# QwDbManager.py
import datetime

import pymysql
from typing import List, Tuple, Optional, Dict, Any

from Util.zconfig import get_setting


class QwDbManager:
    """
    企微审批工具专用数据库管理类
    """

    def __init__(self):
        # 根据 DEBUG 判断用 DEV 还是 PRD
        debug = get_setting('DEBUG')
        self.dburl = get_setting('DBURL')['DEV'] if debug else get_setting('DBURL')['PRD']

        # 固定其余参数
        self._cfg = dict(
            host=self.dburl,
            user='root',
            password='123',
            database='ecadb',
            charset='utf8mb4',
            port=3306
        )

    # ---------- 通用 ----------
    def _get_conn(self) -> pymysql.Connection:
        return pymysql.connect(**self._cfg)

    # ---------- 人员 ----------
    def query_person_names(self) -> dict:
        conn = None
        try:
            conn = self._get_conn()
            with conn.cursor() as cur:
                cur.execute("SELECT user_name FROM auto_person ORDER BY id")
                names = [row[0] for row in cur.fetchall()]
            return {'msg': None, 'data': names}
        except Exception as e:
            return {'msg': str(e), 'data': []}
        finally:
            if conn:
                conn.close()

    def import_persons(self, datas: List[str]) -> Tuple[int, Optional[str]]:
        conn = None
        try:
            conn = self._get_conn()
            with conn.cursor() as cur:
                cur.execute("TRUNCATE TABLE auto_person")
                sql = "INSERT INTO auto_person (user_name) VALUES (%s)"
                cur.executemany(sql, [(name,) for name in datas])
                conn.commit()
            return len(datas), None
        except Exception as e:
            if conn:
                conn.rollback()
            return 0, str(e)
        finally:
            if conn:
                conn.close()

    # ---------- 任务 ----------
    def create_today_tasks(self) -> Tuple[int, Optional[str]]:
        today = datetime.date.today()
        now = datetime.datetime.now()
        try:
            with self._get_conn() as conn:
                with conn.cursor() as cur:
                    # 1. 人员列表
                    cur.execute("SELECT user_name FROM auto_person ORDER BY id")
                    persons = [row[0] for row in cur.fetchall()]
                    if not persons:
                        return 0, "暂无人员"
                    # 2. 清空今日任务
                    cur.execute("DELETE FROM auto_download_status WHERE task_date = %s", (today,))
                    # 3. 批量插入
                    cur.executemany(
                        "INSERT INTO auto_download_status "
                        "(user_name, task_date, task_run_time, task_count, task_flag) "
                        "VALUES (%s, %s, %s, %s, %s)",
                        [(p, today, now, 0, 'false') for p in persons]
                    )
                    conn.commit()
                    return len(persons), None
        except Exception as e:
            return 0, str(e)

    def query_tasks(
            self,
            *,
            user_name: Optional[str] = None,
            start_date: Optional[datetime.date] = None,
            end_date: Optional[datetime.date] = None,
            task_flag: Optional[str] = None
    ) -> Tuple[List[tuple], Optional[str]]:
        """
        动态拼接 WHERE 条件查询 auto_download_status
        参数全 None → 查全部
        """
        sql = """
            SELECT user_name, task_date, task_run_time, task_count, task_flag
            FROM auto_download_status
        """
        where_parts = []
        params = []

        if user_name is not None:
            where_parts.append("user_name = %s")
            params.append(user_name)
        if start_date is not None:
            where_parts.append("task_date >= %s")
            params.append(start_date)
        if end_date is not None:
            where_parts.append("task_date <= %s")
            params.append(end_date)
        if task_flag is not None:
            where_parts.append("task_flag = %s")
            params.append(task_flag)

        if where_parts:
            sql += " WHERE " + " AND ".join(where_parts)
        sql += " ORDER BY id"

        conn = None
        try:
            conn = self._get_conn()
            with conn.cursor() as cur:
                cur.execute(sql, params)
                return cur.fetchall(), None
        except Exception as e:
            return [], str(e)
        finally:
            if conn:
                conn.close()

    def today_tasks_exist(self, today):
        """
        返回 (是否已存在, 今天已下载人数)
        """
        sql = """
            SELECT COUNT(DISTINCT user_name)
            FROM auto_download_status
            WHERE task_date = %s
        """
        with self._get_conn().cursor() as cur:
            cur.execute(sql, (today,))
            cnt = cur.fetchone()[0]
        return bool(cnt), cnt

    def save_download_status(self, user_name: str, task_count: int) -> None:
        """
        无论有数据/无数据，统一写 auto_download_status
        :param user_name:  人员姓名
        :param task_count: 本次抓取到的单据数量，无数据时传 0
        """
        today = datetime.date.today()
        # flag = 'true' if task_count > 0 else 'false'
        flag = 'true'

        sql = """
        INSERT INTO auto_download_status
            (user_name, task_date, task_run_time, task_count, task_flag)
        VALUES
            (%s, %s, NOW(), %s, %s)
        ON DUPLICATE KEY UPDATE
            task_run_time = VALUES(task_run_time),
            task_count    = VALUES(task_count),
            task_flag     = VALUES(task_flag)
        """

        with self._get_conn().cursor() as cur:
            cur.execute(sql, (user_name, today, task_count, flag))
            cur.connection.commit()

    def save_task_info(self, datas: List[Dict[str, str]]) -> None:
        """
        批量写入或更新 auto_task_info
        :param datas: 每条 dict 必须含 user_name, task_id, task_name, task_add_time
        """
        select_sql = """
            SELECT task_sum
            FROM auto_task_info
            WHERE user_name = %s AND task_id = %s
            LIMIT 1
        """

        insert_sql = """
            INSERT INTO auto_task_info
                (user_name, task_id, task_name, task_add_time,
                 task_get_time, task_sum, task_first_time)
            VALUES
                (%(user_name)s, %(task_id)s, %(task_name)s,
                 %(task_add_time)s, NOW(), 0, NOW())
        """
        # 增加限制,每天第一次下载到时才累加24小时  LJ 20250829==>修改为每天第一次下载时才累加24小时,并且不影响最后一次更新时间
        update_sql = """
            UPDATE auto_task_info
            SET task_sum = CASE
                               WHEN DATE(task_get_time) < CURDATE() THEN task_sum + 24
                               ELSE task_sum
                           END,
                task_get_time = NOW()
            WHERE user_name = %s AND task_id = %s
        """

        conn = self._get_conn()
        try:
            with conn.cursor() as cur:
                for row in datas:
                    # 查重
                    cur.execute(select_sql, (row['user_name'], row['task_id']))
                    exists = cur.fetchone()

                    if exists is None:
                        # 首次下载 → 插入
                        cur.execute(insert_sql, row)
                    else:
                        # 已存在 → 更新
                        cur.execute(update_sql, (row['user_name'], row['task_id']))
            conn.commit()
        except Exception:
            conn.rollback()
            raise
        finally:
            conn.close()

    def query_all_task_info(self) -> Tuple[List[Dict[str, Any]], str]:
        sql = """
            SELECT user_name,
                   task_id,
                   task_name,
                   task_add_time,
                   task_get_time,
                   task_sum,
                   task_first_time
            FROM auto_task_info
            ORDER BY user_name, task_add_time
        """
        try:
            conn = self._get_conn()
            with conn.cursor() as cur:
                cur.execute(sql)
                columns = [col[0] for col in cur.description]
                rows = [dict(zip(columns, row)) for row in cur.fetchall()]
            return rows, ""
        except Exception as e:
            return [], str(e)
